package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map.Entry;
import java.util.Set;

import beans.Paragraphe;
import beans.Terme;

/**
 * 
 * @author Ibrahima Sory Diallo
 */
public class AppUtil implements AppUtilInterface {
	
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public void connectBase(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost/rechercheinfo?"
		            + "user=root&password=");
			
			statement = connect.createStatement();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}

	}

	//Fermeture du resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
      		}

			if (statement != null) {
		        statement.close();
			}

			if (connect != null) {
		        connect.close();
			}
    	}
		catch (Exception e) {

		}
	}

	@Override
	public int getIdterme(String terme) throws Exception {
		int id=0;
		try {
	    		    
		    resultSet = statement.executeQuery("Select idTerme from `rechercheinfo`.`termes` where `terme` = '" + terme + "'");
		    
		    if (resultSet.next()){
		    	id = resultSet.getInt("idTerme");
		    }
		    return id;
		    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}
	
	@Override
	public void insertTermes(Set<String> termes) throws Exception {
		
	    try {

		    //Requete d'insertion
	    	String srequest = "INSERT IGNORE INTO `rechercheinfo`.`termes` (`terme`) VALUES ";
	    	for (String terme : termes)
	    		srequest += "('" + terme + "') ,";
			srequest = srequest.substring(0, srequest.lastIndexOf(","));
	    	
		    preparedStatement = connect.prepareStatement(srequest);
		    //preparedStatement.setString(2, terme);
		    preparedStatement.executeUpdate();
	      
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public void insertParagraphes(ArrayList<Paragraphe> paragraphes)
			throws Exception {
		try {
	    	
			String srequest = "INSERT INTO `rechercheinfo`.`paragraphes` (`filePath`,`xPath`,`longueur`) VALUES ";
			for (Paragraphe paragraphe : paragraphes)
				srequest += "('" + paragraphe.getFilepath() + "','" + paragraphe.getXpath() + "','" + paragraphe.getLongueur() + "') ,";
			srequest = srequest.substring(0, srequest.lastIndexOf(","));
			
		    preparedStatement = connect.prepareStatement(srequest);

		    preparedStatement.executeUpdate();
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}

	@Override
	public int getNbLigneTermes() throws Exception {
		
		int nbLignes = 0;
		try {
	    	
		    resultSet = statement.executeQuery("Select count(idTerme) as NbLignes from `rechercheinfo`.`termes`");		    
		    if (resultSet.next()){
		    	nbLignes = resultSet.getInt("NbLignes");
		    }
		    return nbLignes;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}

	}

	@Override
	public void setIdfTerme(int idTerme, double idf) throws Exception {
		//UPDATE  `rechercheinfo`.`termes` SET  `idf` =  '12.5' WHERE  `termes`.`idTerme` =13

		try {
		    preparedStatement = connect.prepareStatement("UPDATE `rechercheinfo`.`termes` SET `idf` = '"+ idf +"' WHERE  `termes`.`idTerme` = '"+idTerme+"'");
			 
		    preparedStatement.executeUpdate();
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}

	}

	@Override
	public void setIdfTermes() throws Exception {

		try {
			double tailleCollection = (double)this.getNbLigneParagraphes();
			int nbTermes = this.getNbLigneTermes();
			
			for (int i=0; i<nbTermes; i+=1000) {
				String srequest = "insert into `rechercheinfo`.`termes` (`idTerme`, `terme`, `idf`) values ";
				
				resultSet = statement.executeQuery("SELECT * FROM `rechercheinfo`.`termes` LIMIT " + i + " , " + 1000);
				HashMap<Integer, String> termesHashMap = new HashMap<Integer, String>();
			    while (resultSet.next()){
			    	termesHashMap.put(resultSet.getInt("idTerme"), resultSet.getString("terme"));
			    }
			    
			    for (Entry<Integer, String> line : termesHashMap.entrySet()) {
			    	int idTerme = line.getKey();
			    	String terme = line.getValue();
			    	double nbParagraphes = this.getNbParagraphePourTerme(idTerme);
			    	double idf;
			    	if (nbParagraphes == 0.0)
			    		idf = 0.0;
			    	else
			    		idf = Math.log(tailleCollection/nbParagraphes);
			    	
			    	srequest += "('" + idTerme + "', '" + terme + "', '" + idf + "'), ";
			    }
			    
			    srequest = srequest.substring(0, srequest.lastIndexOf(","));
				srequest += " on duplicate key update idf=values(idf)";
			    preparedStatement = connect.prepareStatement(srequest);
				
			    preparedStatement.executeUpdate();
			}
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public double getNbParagraphePourTerme(int idTerme) throws Exception {
		int nbParagraphe = 0;
		try {
	    	
		    resultSet = statement.executeQuery("SELECT count(paragraphes.idParagraphe) as nbParagraphe FROM `paragraphes` INNER JOIN `apourposition` ON `paragraphes`.`idParagraphe` = `apourposition`.`idParagraphe` WHERE `apourposition`.`idterme` = '"+idTerme+"'");		    
		    
		    if (resultSet.next()){
		    	nbParagraphe = resultSet.getInt("nbParagraphe");
		    }
		    return nbParagraphe;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public int getNbLigneParagraphes() throws Exception {
		int nbLignes = 0;
		try {
	    	
		    resultSet = statement.executeQuery("Select count(idParagraphe) as NbLignes from `rechercheinfo`.`paragraphes`");		    
		    if (resultSet.next()){
		    	nbLignes = resultSet.getInt("NbLignes");
		    }
		    return nbLignes;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	
	@Override
	public int getIdParagraphe(String filepath, String xpath) throws Exception {
		int id=0;
		try {
	    	
		    
		    resultSet = statement.executeQuery("Select idParagraphe from `rechercheinfo`.`paragraphes` where `filePath` = '"+ filepath +"' AND `xPath` = '"+ xpath +"'");
		    
		    if (resultSet.next()){
		    	id = resultSet.getInt("idParagraphe");
		    }
		    return id;
		    
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public double getLongueurMoyParagraphes() throws Exception {
		double moyenne = 0.0;
		try {
		    resultSet = statement.executeQuery("Select AVG(longueur) as moyenne From `paragraphes`");		    
		    if (resultSet.next()){
		    	moyenne = resultSet.getDouble("moyenne");
		    }
		    return moyenne;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public int getNbLigneApparaitDans() throws Exception {
		int nbLignes = 0;
		try {
	    	
		    resultSet = statement.executeQuery("Select count(idApparaitDans) as NbLignes from `rechercheinfo`.`apparaitdans`");		    
		    if (resultSet.next()){
		    	nbLignes = resultSet.getInt("NbLignes");
		    }
		    return nbLignes;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public void insertApparaitDans(Paragraphe paragraphe) throws Exception {
		try {
	    	
			
			String srequest = "INSERT INTO `rechercheinfo`.`apparaitdans` (`idTerme`,`idParagraphe`,`freq`) VALUES ";
					
			//Pour chaque terme, insertion des liens terme-paragraphe (2 tables)
			HashMap<String, Terme> pHashmap = paragraphe.getTermeMap();
			for (String s : pHashmap.keySet()) {
				Terme terme = pHashmap.get(s);
				//System.out.println("terme Id: "+ terme.getId());
				srequest += "('" + terme.getId() + "','" + paragraphe.getId() + "','" + terme.getFrequence() + "') ,";
			}
			srequest = srequest.substring(0, srequest.lastIndexOf(","));
		 
		    preparedStatement = connect.prepareStatement(srequest);
		 
		    preparedStatement.executeUpdate();
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}

	@Override
	public void setTfApparaitDans(int idApparaitDans, double tf)
			throws Exception {
		try {
	    	
		    preparedStatement = connect.prepareStatement("UPDATE `rechercheinfo`.`apparaitdans` SET `tf` = '"+ tf +"' WHERE  `apparaitdans`.`idApparaitDans` = '"+idApparaitDans+"'");
			 
		    preparedStatement.executeUpdate();
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public void setTfApparaitDans(HashMap<Integer, Double> tfHashmap)
			throws Exception {
		try {
			String srequest_base = "insert into `rechercheinfo`.`apparaitdans` (`idApparaitDans`, `idTerme`, `idParagraphe`, `freq`, `tf`) values ";
			String srequest = srequest_base;
			int numLigne = 1;
			for (Entry<Integer, Double> line : tfHashmap.entrySet()) {
				resultSet = statement.executeQuery("Select idTerme, idParagraphe, freq from `rechercheinfo`.`apparaitdans` where `idapparaitdans` = '" + line.getKey() + "'");
			    
				int idTerme = 0;
				int idParagraphe = 0;
				double freq = 0;
			    if (resultSet.next()){
			    	idTerme = resultSet.getInt("idTerme");
			    	idParagraphe = resultSet.getInt("idParagraphe");
			    	freq = resultSet.getDouble("freq");
			    }
				
				srequest += "('" + line.getKey() + "', '" + idTerme + "', '" + idParagraphe + "', '" + freq + "', '" + line.getValue() + "'), ";
				
				if (numLigne%1000 == 0) {
					srequest = srequest.substring(0, srequest.lastIndexOf(","));
					srequest += " on duplicate key update tf=values(tf)";
				    preparedStatement = connect.prepareStatement(srequest);
				    preparedStatement.executeUpdate();
				    
				    srequest = srequest_base;
				}
					
				numLigne++;
			}
			srequest = srequest.substring(0, srequest.lastIndexOf(","));
			srequest += " on duplicate key update tf=values(tf)";
		    preparedStatement = connect.prepareStatement(srequest);
			
		    preparedStatement.executeUpdate();
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public double[] getFreqEtLongueur(int idApparaitDans) throws Exception {
		double [] tab = new double[2];

		try {
		    
		    resultSet = statement.executeQuery("Select freq from `rechercheinfo`.`apparaitdans` where `idapparaitdans` = '" + idApparaitDans + "'");
		    
		    if (resultSet.next()){
		    	tab[0] = resultSet.getDouble("freq");
		    }
		    resultSet = statement.executeQuery("Select `paragraphes`.`longueur` FROM `paragraphes` INNER JOIN `apparaitdans` ON `paragraphes`.`idParagraphe` = `apparaitdans`.`idParagraphe` WHERE `apparaitdans`.`idapparaitdans` ='" + idApparaitDans + "'");
		    if (resultSet.next()){
		    	tab[1] = (double) resultSet.getInt("longueur");
		    }
		    //System.out.println("long = "+tab[1]);
		    return tab;
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}

	@Override
	public void insertAPourPosition(Paragraphe paragraphe)
			throws Exception {
		try {

			
			String srequest = "INSERT INTO `rechercheinfo`.`apourposition` (`idTerme`,`idParagraphe`,`position`) VALUES ";
			
			//Pour chaque terme, insertion des liens terme-paragraphe (2 tables)
			HashMap<String, Terme> pHashmap = paragraphe.getTermeMap();
			for (String s : pHashmap.keySet()) {
				Terme terme = pHashmap.get(s);
				for (int position : terme.getPosition())
					srequest += "('" + terme.getId() + "','" + paragraphe.getId() + "','" + position + "') ,";
			}
			srequest = srequest.substring(0, srequest.lastIndexOf(","));
			
		    preparedStatement = connect.prepareStatement(srequest);
		    preparedStatement.executeUpdate();
	    
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}

	@Override
	public boolean termeExists(String terme) throws Exception {
		// SELECT 1		FROM  `termes` 		WHERE  `terme` =  'toto'
		boolean exist = false;
		try {
		    resultSet = statement.executeQuery("Select 1 from `rechercheinfo`.`termes` where `terme`= '"+terme+"'");		    
		    if (resultSet.next()){
		    	exist = resultSet.getBoolean("1");
		    }
		    return exist;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}

	@Override
	public double getTfIdf(int idParagraphe, String terme) throws Exception {
		double tfIdf = 0.0;
		try {
			resultSet = statement.executeQuery("Select `termes`.`idf`, `apparaitdans`.`tf` FROM `termes` INNER JOIN `apparaitdans` ON `termes`.`idterme` = `apparaitdans`.`idterme` WHERE `apparaitdans`.`idparagraphe` ='"+idParagraphe+"' AND `termes`.`terme` = '"+terme+"'");
		    
		    if (resultSet.next()){
		    	tfIdf = resultSet.getDouble("tf") * resultSet.getDouble("idf");
		    }
		    
		    return tfIdf;
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

	@Override
	public String[] getParagraphePaths(int idParagraphe) throws Exception {
		String [] tab = {"",""};

		try {
			
		    
		    resultSet = statement.executeQuery("Select `xPath`, `filePath` from `rechercheinfo`.`paragraphes` where `idparagraphe` = '" + idParagraphe + "'");
		    
		    if (resultSet.next()){
		    	tab[0] = resultSet.getString("filePath");
		    	tab[1] = resultSet.getString("xPath");
		    }

		    //System.out.println("tableau = "+tab[0].toString()+"--"+tab[1].toString());
		    return tab;
	    } 
	    catch (Exception e) {
		   	throw e;
		}
		
	}
	 
	@Override
	public boolean isFollowed(int idParagraphe, String terme1, String terme2) throws Exception{
		
		boolean followed = false;
		try {
			resultSet = statement.executeQuery("select 1 from (Select `apourposition`.`position` as pos1 FROM `apourposition` INNER JOIN `termes` ON `apourposition`.`idTerme` = `termes`.`idTerme` WHERE `apourposition`.`idParagraphe` = '"+idParagraphe+"' AND `termes`.`terme`= '"+terme1+"') as Position1, (Select `apourposition`.`position` as pos2 FROM `apourposition` INNER JOIN `termes` ON `apourposition`.`idTerme` = `termes`.`idTerme` WHERE `apourposition`.`idParagraphe` = '"+idParagraphe+"' AND `termes`.`terme`= '"+terme2+"') as Position2	where Position2.pos2 = Position1.pos1 + 1 ");		    
		    
		    if (resultSet.next()){
		    	followed = resultSet.getBoolean("1");
		    }
		    return followed;
		
	    } 
	    catch (Exception e) {
		   	throw e;
		}
	}

}
